package com.scalar.forecast.forecastSetup.dao;

import com.scalar.forecast.forecastSetup.forms.UserDefinedFieldsForm;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.struts.util.LabelValueBean;

/**
 * User: Sujan Kumar
 * Date: Feb 9, 2005
 * Time: 6:32:57 PM
 */
public class ForecastUserFieldsDAO
{
    public static boolean loadUserDefinedFields(DBTransaction dbTransaction, UserDefinedFieldsForm userDefinedFieldsForm) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.userFields.loadUserDefinedFields" ) );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                userDefinedFieldsForm.setUserList1( rset.getString( "UserList1" ) );
                userDefinedFieldsForm.setUserDate1( rset.getString ( "UserDate1" ) );
                userDefinedFieldsForm.setUserText1( rset.getString ( "UserTxt1" ) );
                userDefinedFieldsForm.setUserText2( rset.getString( "UserTxt2" ) );
            }
            rset.close();
            pstmt.close();
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.userFields.loadUserFieldValues" ) );
            pstmt.setString ( 1, "FCUSERENTRY" );
            pstmt.setString ( 2, userDefinedFieldsForm.getUserList1() );
            rset = pstmt.executeQuery();
            ArrayList userList1Values = new ArrayList();

            while ( rset.next() )
            {
                userList1Values.add ( rset.getString( "EntryValue" ) );
            }
            int listSize = userList1Values.size();
            String listValues[] = new String[ listSize ];
            userList1Values.toArray( listValues );
            userDefinedFieldsForm.setUserList1Values( listValues );
            success = true;
        }
        catch ( Exception e )
        {
            success = false;
            System.out.println ( "-----Exception While loading UserDefinedFieldsForm----" );
            e.printStackTrace();
            throw new ScalarException ( "Exception While loading UserDefinedFieldsForm" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return success;
    }
    public static boolean saveUserDefinedFields (DBTransaction dbTransaction, String userList1Name, String userDate1Name, String userTxt1Name, String userTxt2Name) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall ( ForecastSqlQueryMappings.get ( "forecast.userFields.saveUserFields" ) );
            cstmt.setString ( 1, userList1Name == null ? "" : userList1Name.trim()  );
            cstmt.setString ( 2, userDate1Name == null ? "" : userDate1Name.trim() );
            cstmt.setString ( 3, userTxt1Name == null ? "" : userTxt1Name.trim() );
            cstmt.setString ( 4, userTxt2Name == null ? "" : userTxt2Name.trim() );
            cstmt.execute();
            success = true;
        }
        catch ( Exception e )
        {
            success = false;
            System.out.println ( "--Exception while saving Forecast UserEntry Fields---" );
            e.printStackTrace();
            throw new ScalarException( "Could Not Save Forecast User Entry Fields");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return success;
    }
    public static boolean saveUserDefinedFieldValues (DBTransaction dbTransaction, String tableName, String userListName, String[] userListValues) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.userFields.deleteUserFieldValues" ) );
            pstmt.setString ( 1, tableName );
            pstmt.setString ( 2, userListName );
            pstmt.executeUpdate();

            pstmt.close();
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.userFields.insertUserFieldValue") );
            if ( userListValues != null )
            {
                int listSize = userListValues.length;
                String userValue = "";
                for ( int i = 0; i < listSize; i++ )
                {
                    userValue = userListValues[i] == null ? "" : userListValues[i].trim();
                    if ( userValue.length() != 0 )
                    {
                        pstmt.setString ( 1, tableName );
                        pstmt.setString ( 2, userListName );
                        pstmt.setString ( 3, userListValues[i] );
                        pstmt.addBatch();
                    }
                }
                pstmt.executeBatch();
            }
            success = true;
        }
        catch ( Exception e )
        {
            success = false;
            System.out.println ( "---Exception while saving the Forecast UserDefined Field Values---" );
            e.printStackTrace();
            throw new ScalarException ( "Exception While Saving Forecast UserDefined Field Values" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return success;
    }
    public static ArrayList getUserFieldValues (DBTransaction dbTransaction, String tableName, String entryName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        LabelValueBean fieldValue = null;
        ArrayList fieldValues = new ArrayList();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.userFields.loadUserFieldValues" ) );
            pstmt.setString ( 1, tableName );
            pstmt.setString ( 2, entryName );
            rset = pstmt.executeQuery();
            String entryValue = null;
            while ( rset.next() )
            {
                entryValue = rset.getString ( "EntryValue");
                fieldValue = new LabelValueBean ( entryValue, entryValue );
                fieldValues.add ( fieldValue );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while loading the User Field Values----" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load User Field Values" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return fieldValues;
    }
    public static String getEntryNameForUserField (DBTransaction dbTransaction, String userFieldColumn) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String entryName = null;
        HashMap map = new HashMap();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.forecastEntry.loadUserFields" ) );
            rset = pstmt.executeQuery ();
            if ( rset.next() )
            {
                map.put( Constants.COLUMN_USERLIST1, rset.getString( "UserList1" ) );
                map.put( Constants.COLUMN_USERDATE1, rset.getString( "UserDate1" ) );
                map.put( Constants.COLUMN_USERDATE1, rset.getString( "UserTxt1" ) );
                map.put( Constants.COLUMN_USERDATE1, rset.getString( "UserTxt2" ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while getting the Entry Name for the UserField--" );
            e.printStackTrace();
            throw new ScalarException ( "Could Not Get EntryName for UserField" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        entryName = ( String )map.get( userFieldColumn );
        return entryName;
    }

}
